package com.gopay.cron.web.controller;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.gopay.common.cipher.utils.LoggerMaskUtils;
import com.gopay.common.cipher.utils.SensitiveInfoUtils;
import com.gopay.common.constants.trans.PayChannel;
import com.gopay.common.constants.trans.TxnStaCode;
import com.gopay.common.constants.txncd.IntTxnCd;
import com.gopay.common.domain.user.identify.PersCertChannelDTL;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.common.user.dao.PersCertChannelDtlDao;
import com.gopay.common.user.manager.PersonThreeAccountChannelManager;
import com.gopay.remote.matcher.BankCodeQueryRemoteService;
import com.gopay.remote.matcher.vo.GopayBankInfoVO;
import com.gopay.scheduler.util.EpCardSecurityUtil;

import cn.topca.util.LoggerUtil;

/**
 * @author zhaojunting
 * 处理快捷支付 存量数据 插入 渠道认证dtl表
 */
@Controller
public class ExpressBankNoChannelController {

	private Logger log = Logger.getLogger(this.getClass());
	
	@Resource(name = "hibernateTemplate")
	private HibernateTemplate template;
	
	@Autowired
	private PersCertChannelDtlDao persCertChannelDtlDao;
	
	@Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;
	
	@Resource(name = "bankCodeQueryClient")
    private BankCodeQueryRemoteService bankCodeQueryClient;
	
    @Resource(name = "personThreeAccountChannelManager")
    private PersonThreeAccountChannelManager personThreeAccountChannelManager;
	
	@RequestMapping("/express/dtl")
	public String doService(){
		log.info("执行快捷支付银行卡存量数据插入实名认证DTL表---start");
		//1.拼装快捷支付存量数据查询sql
		String sql = getQueryString();
		
		//2.查询
		List<Object[]> result = doQuery(sql);
		log.info("查询出关联数据结果集result = " + result.size() + "条; ");
		
		//3.解析（解密）, 并组装实名认证流水表数据
		List<PersCertChannelDTL> dtls = decrypt(result);
		log.info("需要插入到DTL中的认证数据 dtls = " + dtls.size() + " 条; ");
		
		//4.插入流水表
		batchInsert(dtls);
		log.info("执行快捷支付银行卡存量数据插入实名认证DTL表---end");
		return "index";
	}
	
	/**
	 * 获取快捷支付存量数据关联SQL
	 * @return
	 */
	private String getQueryString(){
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT t1.cust_id, t1.cert_no cert_no1, t2.code, t1.bank_acct_num, t3.cert_no cert_no2");
		sb.append(" FROM  ep_card_bind_info_sec t1");
		sb.append(" LEFT JOIN  dic_gopay_bank_info t2");
		sb.append(" ON t1.bank_code = t2.abbr_code");
		sb.append(" LEFT JOIN cust_person_info t3");
		sb.append(" ON t1.cust_id = t3.cust_id");
		sb.append(" WHERE t1.bind_state != '2' ");
		
		return sb.toString();
	}
	
	/**
	 * 执行查询
	 * @param sql
	 * @return
	 */
	private List<Object[]> doQuery(final String sql){
		List<Object[]> list = (List<Object[]>)template.execute(new HibernateCallback<Object>() {
            @Override
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(sql);

                return query.list();
            }
        });
		return list;
	}

	/**
	 * 解析结果集， 并拼装需要插入的流水表数据
	 * @param result
	 * @return
	 */
	private List<PersCertChannelDTL> decrypt(List<Object[]> result){
		List<PersCertChannelDTL> dtls = new ArrayList<PersCertChannelDTL>();
		
		for(Object[] record : result){
			//解析行记录
			PersCertChannelDTL dtl = decrypt(record);
			if(dtl != null){
				dtls.add(dtl);
			}
		}
		return dtls;
	}
	
	/**
	 * 处理单条
	 * @param record
	 * @return
	 */
	private PersCertChannelDTL decrypt(Object[] record){
		String custId = (String)record[0];
		String certNo = (String)record[1];
		String code = (String)record[2];
		String bankNoSec = (String)record[3];
		String realCertNo = (String)record[4];
		String bankNo = SensitiveInfoUtils.smartDecryptWithCheck(bankNoSec);	//解密银行卡号
		certNo =SensitiveInfoUtils.smartDecryptWithCheck(certNo);			//解密身份证号
		log.info("Deal with record : custId="+custId+",certNo="+certNo+",channelCode="+code+",bankNoSec="+bankNoSec+",bankNo="+ LoggerMaskUtils.maskString(bankNo)+ ",realCertNo = " + realCertNo + ";");
		
		if(null != realCertNo && !realCertNo.equals(certNo)){
			log.info("cust_person_info certNo = "+realCertNo +",ep_card_bind_info_sec certNo = " + certNo + ", 不匹配. 不作处理");
			return null;
		}
		//流水表存在记录 ， 不作处理
		if(null != persCertChannelDtlDao.findByCardNoAndChannel(custId, bankNo, code)){
			log.info("DTL中已存在 custId = " + custId + ",bankNo = "+ LoggerMaskUtils.maskString(bankNo) + ",channel = " + code + "已存在！" );
			return null;
		}
		PersCertChannelDTL dtl = new PersCertChannelDTL();
		dtl.setCustId(custId);
		dtl.setCertNo(certNo);
		dtl.setCardNo(bankNo);
		dtl.setChannelCode(code);
		dtl.setStatus("01");
		dtl.setCreatedTs(new Date());
		dtl.setRemark("express old data insert");
		return dtl;
	}
	/**
	 * 批量插入数据
	 * @param dtls
	 */
	private void batchInsert(List<PersCertChannelDTL> dtls){
		template.saveOrUpdateAll(dtls);
	}


	//提现存量
	@RequestMapping("/withdraw/dtl")
	public String doWithdraw() {
		log.error("开始处理个人提现成功的银行信息存量数据！");
		//查询出当日个人提现成功的信息
		List<Object[]> personDrawCard=findPersonDrawCard();
		for(Object[] o : personDrawCard){
		  //返回对应银行卡code与银行卡号不为空的时候，插入认证日志表
		  if(o[2] != null && o[3] != null){
			  GopayBankInfoVO bankInfoResult=bankCodeQueryClient.getBankInfoByName(o[2].toString());
				  //判断认证日志表中有没有该用户的这个银行卡信息
				  PersCertChannelDTL persCertChannelDtl=persCertChannelDtlDao.findByCardNoAndChannel(o[1].toString(),o[3].toString(),bankInfoResult==null?o[2].toString():bankInfoResult.getCode());
				  log.error("该订单号是"+o[4].toString()+",银行名称是"+o[2].toString()+",银行卡号是"+o[3].toString()+"");
				  if(persCertChannelDtl == null){
					  PersCertChannelDTL PersCertChannel =new PersCertChannelDTL();//new一个新的domain
					  PersCertChannel.setCertNo(o[0]==null?"":o[0].toString());
					  PersCertChannel.setCustId(o[1].toString());
					  PersCertChannel.setChannelCode(bankInfoResult==null?o[2].toString():bankInfoResult.getCode());
					  PersCertChannel.setStatus("01");
					  PersCertChannel.setCreatedOperId(o[1].toString());
					  PersCertChannel.setCreatedTs(new Date());
					  PersCertChannel.setCardNo(o[3].toString());
					  PersCertChannel.setRemark("提现存量银行卡认证渠道");
					//addby boai 三类账户保存渠道信息统一口径
//                    persCertChannelDtlDao.save(persCertChannelDTL);
                      personThreeAccountChannelManager.savePersCertChannel(PersCertChannel);
                      ////  修改日誌的輸出，ssj 2017/01/12 pci需求改造，銀行卡號掩碼輸出
					  log.error("该订单号是"+o[4].toString()+",银行名称是"+o[2].toString()+",银行卡号是"+LoggerMaskUtils.maskString(o[3].toString())+",往认证日志表中插入数据。");
				  }else{
					   //修改日誌的輸出，ssj 2017/01/12 pci需求改造，銀行卡號掩碼輸出
					  log.error("该订单号是"+o[4].toString()+",银行名称是"+o[2].toString()+",银行卡号是"+LoggerMaskUtils.maskString(o[3].toString())+",认证日志表中已有记录，不插入数据。");
				  }
		   }
		}
		log.error("结束处理个人提现成功的银行信息存量数据！");
		
		return "index";
	}
	
	
	
	
	
	//查询出单日个人提现信息
	public List<Object[]> findPersonDrawCard(){
    	Map<String, Object> param = new HashMap<String, Object>();
    	param.put("payChannel", PayChannel._02.value);
    	param.put("intTxnCd", IntTxnCd._00800.value);
    	param.put("txnStaCode",String.valueOf(TxnStaCode.TXN_STA_CD_20000.value));
    	
    	
    	StringBuffer sql = new StringBuffer(
    	 "select t3.cert_no,t3.cust_id,t2.recv_bank_name, t2.recv_bank_acct_num as card_no,t1.gopay_order_id" +
    	 " from cps_gen_main_order t1, cps_gen_pay_order t2, cust_person_info t3" +
    	 " where t1.gopay_order_id = t2.gopay_order_id and t1.pay_cust_id = t3.cust_id"+
    	 " and t1.pay_channel=:payChannel and t1.gopay_int_txn_cd=:intTxnCd and t1.txn_sta_cd=:txnStaCode and t3.cert_no is not null " );
    	
    	return commonQueryDAO.findBySql(sql.toString(),param);	
    }
}
